package com.icetec.iceintel.database;


import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import java.util.ArrayList;

import com.icetec.iceintel.model.Device;
import com.icetec.iceintel.model.Scheme;
import com.icetec.iceintel.utility.StrProUtility;

/**
 * Created by amo on 2014/12/20.
 * 模式控制
 * 单个模式由用户创建,单个模式ID指定唯一的模式
 * 模式下有多个灯具的MAC，灯具状态灯信息
 */
public class SchemeControlSqlHelper {

    private ApplicationSQLiteHelper applicationSQLiteHelper=ApplicationSQLiteHelper.sharedInstance();


    //对于模式控制，每一个模式有一个固定的模式ID，这个每个模式的对象都拥有一个单独的实例
    //每一个模式实例，我们用ID，名字，类型去标识它
    //ID作为主键
    //名字作为用户看到的标识符
    //类型标识模式的图标


    public final static String TABLE_SCHEME="schemetable";
    public final static String TABLE_SCHEME_ID="id";
    public final static String TABLE_SCHEME_NAME="name";
    public final static String TABLE_SCHEME_TYPE="type";


    public final static String CREATE_TABLE_SCHEME = "create table if not exists "+TABLE_SCHEME+" ("
            + TABLE_SCHEME_ID
            + " INTEGER primary key autoincrement , "
            + TABLE_SCHEME_NAME
            + " varchar(20),"
            + TABLE_SCHEME_TYPE
            + " varchar(20)"
            +")";

    public final static String GET_ALL_SCHEME="select "+TABLE_SCHEME_ID+","+TABLE_SCHEME_NAME+","+TABLE_SCHEME_TYPE+" from "+TABLE_SCHEME;
    public final static String GET_SINGEL_SCHEME = "select * from " + TABLE_SCHEME + " where " + TABLE_SCHEME_ID + "=?";
    public final static String UPDATE_SCHEME = "update " + TABLE_SCHEME + " set " + TABLE_SCHEME_NAME + "=?, " + TABLE_SCHEME_TYPE + "=? where " + TABLE_SCHEME_ID + "=?";

    //对于模式关系表，用ID和MAC关联，一个模式ID对应多个灯的MAC，并且记录在特定模式下，这个灯的亮度，色温，开关状态

    public final static String TABLE_SCHEME_RELATION="schemerelation";
    public final static String TABLE_SCHEME_RELATION_ID="id";
    public final static String TABLE_SCHEME_RELATION_MAC="mac";
    public final static String TABLE_SCHEME_RELATION_ONOFF="onoff";
    public final static String TABLE_SCHEME_RELATION_BRIGHTNESS="brightness";
    public final static String TABLE_SCHEME_RELATION_COLORTEMP="colortemp";

    public final static String CREATE_TABLE_SCHEME_RELATION = "create table if not exists "+TABLE_SCHEME_RELATION+" ("
            + TABLE_SCHEME_RELATION_ID
            + " INTEGER , "
            + TABLE_SCHEME_RELATION_MAC
            + " varchar(20),  "
            + TABLE_SCHEME_RELATION_ONOFF
            + " varchar(20),  "
            + TABLE_SCHEME_RELATION_BRIGHTNESS
            + " varchar(20),  "
            + TABLE_SCHEME_RELATION_COLORTEMP
            + " varchar(20)  "
            +")";


    public final static  String GET_SINGLE_SCHEME_RELATION="select "
            +TABLE_SCHEME_RELATION_ID+","
            +TABLE_SCHEME_RELATION_MAC+","
            +TABLE_SCHEME_RELATION_ONOFF+","
            +TABLE_SCHEME_RELATION_BRIGHTNESS+","
            +TABLE_SCHEME_RELATION_COLORTEMP+" "
            + "from "+TABLE_SCHEME_RELATION
            +" where "+TABLE_SCHEME_RELATION_ID+"=?";

    public final static  String GET_SINGLE_SCHEME="select "+TABLE_SCHEME_NAME+","+TABLE_SCHEME_TYPE+" from "+TABLE_SCHEME
            +" where "+TABLE_SCHEME_ID+"=?";

    public final  static  String ADD_SINGLE_SCHEME="insert into "+TABLE_SCHEME+"("+TABLE_SCHEME_NAME+","+TABLE_SCHEME_TYPE+") values(?,?)";

    public final static String UPADE_SINGLE_SCHEME_RELATION = "update "
            + TABLE_SCHEME_RELATION + " set "
            + TABLE_SCHEME_RELATION_ONOFF + "=?, "
            + TABLE_SCHEME_RELATION_BRIGHTNESS + "=?, "
            + TABLE_SCHEME_RELATION_COLORTEMP + "=? where "
            + TABLE_SCHEME_ID +"=? and "
            + TABLE_SCHEME_RELATION_MAC + "=?";

    public final static String LAST_IDSERT_ID="select last_insert_rowid() from "+TABLE_SCHEME;



    public boolean addScheme(Scheme scheme){
        SQLiteDatabase db=applicationSQLiteHelper.getWritableDatabase();
        return addScheme(scheme, db);
    }

    public boolean addScheme(Scheme scheme, SQLiteDatabase db){
        Cursor cursor= db.rawQuery(GET_SINGLE_SCHEME,new String[]{scheme.getId().toString()});

        Log.i("准备调用添加模式","查找");

        //查找这个模式的ID时候已经存在，不存在才会去添加
        if(!cursor.moveToNext()){

            Log.i("准备调用添加模式","没有找到，准备添加");
            db.beginTransaction();

            //对于单个模式，添加进数据库之后拿到这个模式的自增长ID
            ContentValues cv = new ContentValues();
            cv.put(TABLE_SCHEME_NAME,scheme.getName());
            cv.put(TABLE_SCHEME_TYPE,scheme.getType());
            db.insert(TABLE_SCHEME,null,cv);

            cursor.close();
            cursor=db.rawQuery(LAST_IDSERT_ID, null);
            int last_id=0;
            if(cursor.moveToNext()) {
                last_id = cursor.getInt(0);
            }
            cursor.close();

            //拿到自增长ID后，在模式关系映射表中添加映射关系
            for(Device device:scheme.getDeviceArrayList()){
                Log.i("准备添加模式设备", device.getMAC());
                ContentValues cvDevice = new ContentValues();
                cvDevice.put(TABLE_SCHEME_RELATION_BRIGHTNESS,device.getBrightness());
                cvDevice.put(TABLE_SCHEME_RELATION_COLORTEMP,device.getColortemp());
                cvDevice.put(TABLE_SCHEME_RELATION_MAC,device.getMAC());
                cvDevice.put(TABLE_SCHEME_RELATION_ONOFF,device.getOnoff());
                cvDevice.put(TABLE_SCHEME_RELATION_ID,last_id);
                db.insert(TABLE_SCHEME_RELATION,null,cvDevice);
            }

            db.setTransactionSuccessful();
            db.endTransaction();

        }

        else{

            Log.i("准备调用添加模式","找到，不进行任何工作");

        }

        return true;
    }


    public void updateSingleScheme(Scheme scheme){
        SQLiteDatabase db=applicationSQLiteHelper.getWritableDatabase();
        Cursor cursor= db.rawQuery(GET_SINGLE_SCHEME, new String[]{scheme.getId().toString()});
        if(cursor.moveToFirst()){
            Log.i("准备调用更新模式","找到模式，准备更新");
            db.beginTransaction();
            db.execSQL(UPDATE_SCHEME, new String[]{scheme.getName(), scheme.getType(), String.valueOf(scheme.getId())});
            for(Device device : scheme.getDeviceArrayList()){
                db.execSQL(UPADE_SINGLE_SCHEME_RELATION, new String[]{
                        device.getOnoff(),
                        device.getBrightness(),
                        device.getColortemp(),
                        String.valueOf(scheme.getId()),
                        device.getMAC()
                });
            }
            db.setTransactionSuccessful();
            db.endTransaction();
        }else{
            Log.i("准备调用更新模式","无此模式，不进行更新操作");
        }
    }


    public ArrayList<Scheme> getAllDevice(){
        DevicesListSqlHelper devicesListSqlHelper = new DevicesListSqlHelper();
        SQLiteDatabase db=applicationSQLiteHelper.getReadableDatabase();
        Cursor cursor=db.rawQuery(GET_ALL_SCHEME, new String[]{});

        Log.i("模式数据库操作", "开始以ID检索全部模式");
        ArrayList<Scheme> array=new ArrayList<Scheme>();

        db.beginTransaction();

        while (cursor.moveToNext()){

            Scheme scheme=new Scheme();
            scheme.setId(cursor.getInt(cursor.getColumnIndex(TABLE_SCHEME_ID)));
            scheme.setName(cursor.getString(cursor.getColumnIndex(TABLE_SCHEME_NAME)));
            scheme.setType(cursor.getString(cursor.getColumnIndex(TABLE_SCHEME_TYPE)));
            Cursor relationCursor=db.rawQuery(GET_SINGLE_SCHEME_RELATION,new String[]{scheme.getId().toString()});

            while ( relationCursor.moveToNext()){
                Device device=devicesListSqlHelper.getSingleDevice(relationCursor.getString( relationCursor.getColumnIndex(TABLE_SCHEME_RELATION_MAC)));
                device.setBrightness(relationCursor.getString(relationCursor.getColumnIndex(TABLE_SCHEME_RELATION_BRIGHTNESS)));
                device.setColortemp(relationCursor.getString(relationCursor.getColumnIndex(TABLE_SCHEME_RELATION_COLORTEMP)));
                //device.setOnoff( relationCursor.getString( relationCursor.getColumnIndex(TABLE_SCHEME_RELATION_ONOFF)));
                scheme.getDeviceArrayList().add(device);
            }

            if(!relationCursor.isClosed()){
                relationCursor.close();
            }

            array.add(scheme);
        }


        if(!cursor.isClosed()){
            cursor.close();
        }

        db.setTransactionSuccessful();
        db.endTransaction();


        return  array;
    }

    public boolean deleteScheme(Scheme scheme){

        SQLiteDatabase db=applicationSQLiteHelper.getReadableDatabase();

        try {


            db.beginTransaction();
            db.delete(TABLE_SCHEME_RELATION, TABLE_SCHEME_RELATION_ID + "=?", new String[]{scheme.getId().toString()});
            db.delete(TABLE_SCHEME, TABLE_SCHEME_ID + "=?", new String[]{scheme.getId().toString()});

            db.setTransactionSuccessful();
            db.endTransaction();
            return true;

        }
        catch (Exception e){

            return false;

        }


    }

    public Scheme setGetSingleScheme(int id){
        DevicesListSqlHelper devicesListSqlHelper = new DevicesListSqlHelper();
        SQLiteDatabase db=applicationSQLiteHelper.getReadableDatabase();
        Cursor cursor=db.rawQuery(GET_SINGEL_SCHEME, new String[]{String.valueOf(id)});

        Log.i("模式数据库操作", "开始以ID检索全部模式");
        Scheme scheme = new Scheme();

        while (cursor.moveToNext()){
            scheme.setId(cursor.getInt(cursor.getColumnIndex(TABLE_SCHEME_ID)));
            scheme.setName(cursor.getString(cursor.getColumnIndex(TABLE_SCHEME_NAME)));
            scheme.setType(cursor.getString(cursor.getColumnIndex(TABLE_SCHEME_TYPE)));
            Cursor relationCursor=db.rawQuery(GET_SINGLE_SCHEME_RELATION,new String[]{scheme.getId().toString()});
            while ( relationCursor.moveToNext()){
                Device device=devicesListSqlHelper.getSingleDevice(relationCursor.getString( relationCursor.getColumnIndex(TABLE_SCHEME_RELATION_MAC)));
                device.setBrightness(relationCursor.getString(relationCursor.getColumnIndex(TABLE_SCHEME_RELATION_BRIGHTNESS)));
                device.setColortemp(relationCursor.getString(relationCursor.getColumnIndex(TABLE_SCHEME_RELATION_COLORTEMP)));
                //device.setOnoff( relationCursor.getString( relationCursor.getColumnIndex(TABLE_SCHEME_RELATION_ONOFF)));
                scheme.getDeviceArrayList().add(device);
            }
            if(!relationCursor.isClosed()){
                relationCursor.close();
            }
        }

        if(!cursor.isClosed()){
            cursor.close();
        }
        return  scheme;
    }


    public void saveAllDeviceReArrange(ArrayList<Scheme> schemeArrayList){
        SQLiteDatabase db=applicationSQLiteHelper.getWritableDatabase();
        db.beginTransaction();
        db.delete(TABLE_SCHEME,null,null);
        db.delete(TABLE_SCHEME_RELATION,null,null);
        for(Scheme scheme:schemeArrayList){
            addScheme(scheme, db);
        }
        db.setTransactionSuccessful();
        db.endTransaction();

    }
}
